/*
 *                    BioJava development code
 *
 * This code may be freely distributed and modified under the
 * terms of the GNU Lesser General Public Licence.  This should
 * be distributed with the code.  If you do not have a copy,
 * see:
 *
 *      http://www.gnu.org/copyleft/lesser.html
 *
 * Copyright for this code is held jointly by the individual
 * authors.  These should be listed in @author doc comments.
 *
 * For more information on the BioJava project and its aims,
 * or to join the biojava-l mailing list, visit the home page
 * at:
 *
 *      http://www.biojava.org/
 *
 * Created on 15.08.2007
 * @author Asia Grzibovska
 *
 */

package uk.ac.sanger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.mysql.*;

import com.mysql.jdbc.exceptions.*;

public class DatabaseUtilities {

	public static String new_system_name;
	
	public static final String RESULT_OK ="OK";
	
	public DatabaseUtilities() {

	} 
	
	private String getTableNameFromURI(int id){
		// correct name = features_[id]
		String tableName = "features_" + id;
		return tableName;
	}
	
	//get coordinate system ID
	public int getIdFromUri(Connection conn, 
			String coordinateSystemURI)
	{
		int id=-1;
		try{
			
			String sql = "select name,id from coordinate_system where uri= ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1,coordinateSystemURI.trim());

			ResultSet rs = ps.executeQuery();	
            
			//set public static variable
			if (rs.next()) {
				id = rs.getInt("id");
				new_system_name=rs.getString("name");
			}
			else {
				id=-1;
				new_system_name="";
			}
			
			return id;
		}
		catch (SQLException e){
			e.printStackTrace();
			return id;}
	}

	//return public static variable
	public String getSystemName(){
		return new_system_name;
	}
	
	public String saveFeature(Connection conn, FeatureBean[] features,
			                  int systemid)
	{
		//return "OK" if success, otherwise error text
		String reply="";
		
		try{
			
			//DataManager dbM = new DataManager();
			for (int i = 0; i < features.length; i++) {
				FeatureBean f = features[i];
			
				String tableName = getTableNameFromURI(systemid);	
				
				//prepare SQL statement
				String sql = "insert into " + tableName +
				" (featureid, featuretype, method, segmentid, " + 
				"start, end, strand, phase, score, attributes ) "+ 
				"values(?,?,?,?,?,?,?,?,?,?)";
				PreparedStatement pstmt = null; 
				pstmt = conn.prepareStatement (sql);    
				//supply values for prepared statement parameters
				pstmt.setString(1,f.getFeatureid());
				pstmt.setString(2,f.getFeaturetype());
				pstmt.setString(3,f.getMethod());
				pstmt.setString(4,f.getSegment());
				pstmt.setString(5,f.getStart());
				pstmt.setString(6,f.getEnd());
				pstmt.setString(7,f.getStrand());
				pstmt.setString(8,f.getPhase());
				if (f.getScore().length()>0 &!(f.getScore().equalsIgnoreCase("null")))
					pstmt.setFloat(9,Float.valueOf(f.getScore()).floatValue());
				else pstmt.setNull(9, 6);
				// compose attributes field as : link="";note="";group=""
				String links="", notes="",groups="";
				if(!(f.getLinks().equals("")))
					links ="link="+f.getLinks();
				else links="";
				if (!(f.getNotes().equals("")))
					notes ="note="+f.getNotes();
				else notes ="";	
				if (!(f.getGroups().equals("")))
					groups ="group="+f.getGroups();
				else groups ="";	
				if(links.equals("") & notes.equals(""))
					pstmt.setString(10, groups);
				else {
					if (!(links.equals(""))&&!(notes.equals(""))){
						if(groups.equals(""))
							pstmt.setString(10, links+";"+notes);
						else
							pstmt.setString(10, links+";"+notes+";"+groups);
					}
					else{
						if(groups.equals(""))
							pstmt.setString(10, links+notes);
						else 
							pstmt.setString(10, links+notes+";"+groups);
					}
				}
				//execute insert statement
				pstmt.executeUpdate();
				//return OK
				reply=RESULT_OK;
			}
		}
		
		catch (MySQLIntegrityConstraintViolationException e){
			reply="Error! "+e.getMessage();
			System.out.println("Error! "+e.getMessage());}
		catch (SQLException e){
			reply="Error! "+e.getMessage();
			e.printStackTrace();}
		catch (Exception e){
			reply="Error! "+e.getMessage();
			e.printStackTrace();}
		return reply;
	}

	public String updateFeature(Connection conn, FeatureBean[] features,
							  int systemid)
	{
		//return "OK" if success, otherwise error text
		String reply="";
		try{
			//DataManager dbM = new DataManager();
			for (int i = 0; i < features.length; i++) {
				FeatureBean f = features[i];
	
				FeatureBean fb =getFeatureById(conn, f.getUpdate(), systemid);
				
				if (fb.getFeatureid()!=""){
				//save old values in history table
					if (saveHistory(conn, fb, "M", f.getCommitMsg(), systemid,
							f.getUser())){
						String tableName = getTableNameFromURI(systemid);
						//prepare SQL statement
						String sql = "update " + tableName + " set " + 
						"  featuretype= ?, method= ?, segmentid= ?, " + 
						"start= ?, end= ?, strand= ?, phase= ?, score= ?, attributes= ? "+ 
						"where featureid='"+f.getUpdate()+"'";
						PreparedStatement pstmt = null; 
						pstmt = conn.prepareStatement (sql);    
						//supply values for prepared statement parameters
						pstmt.setString(1,f.getFeaturetype());
						pstmt.setString(2,f.getMethod());
						pstmt.setString(3,f.getSegment());
						pstmt.setString(4,f.getStart());
						pstmt.setString(5,f.getEnd());
						pstmt.setString(6,f.getStrand());
						pstmt.setString(7,f.getPhase());
						if (f.getScore().length()>0 & !(f.getScore().equalsIgnoreCase("null")))
							pstmt.setFloat(8,Float.valueOf(f.getScore()).floatValue());
						else pstmt.setNull(8, 6);
						// compose attributes field as : link="";note="";group=""
						String links="", notes="",groups="";
						if(!(f.getLinks().equals("")))
							links ="link="+f.getLinks();
						else links="";
						if (!(f.getNotes().equals("")))
							notes ="note="+f.getNotes();
						else notes ="";	
						if (!(f.getGroups().equals("")))
							groups ="group="+f.getGroups();
						else groups ="";	

						if(links.equals("") & notes.equals(""))
							pstmt.setString(9, groups);
						else {
							if (!(links.equals(""))&&!(notes.equals(""))){
								if(groups.equals(""))
									pstmt.setString(9, links+";"+notes);
								else
									pstmt.setString(9, links+";"+notes+";"+groups);
							}
							else{
								if(groups.equals(""))
									pstmt.setString(9, links+notes);
								else 
									pstmt.setString(9, links+notes+";"+groups);
							}
						}
						//execute insert statement
						pstmt.executeUpdate();
						reply=RESULT_OK;
					}
					else reply = "Error, could not save History table!";
				}
				else {
					//System.out.println("DatabaseUtilities : nothing to update");
					reply = "Error, this feature was not found in Features table!";
				}
			}
		}
		catch (SQLException e){
			e.printStackTrace();
			reply = "Error! " + e.getMessage(); }
		return reply;
	}

	public boolean saveHistory(Connection conn, 
			FeatureBean features, 
			String action, 
			String commit_msg, 
			int systemid,
			String user)
	{
		try{
			//get current time
			java.util.Date today = new java.util.Date();
			java.sql.Timestamp now = new java.sql.Timestamp(today.getTime());
			FeatureBean f = features;
			//prepare SQL statement
			String sql = "insert into history_" + systemid +
			" (action,user,date,commit_msg, featureid, featuretype, method, segmentid, " + 
			"start, end, strand, phase, score, attributes,ref_id ) "+ 
			"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";


			PreparedStatement pstmt = null; 
			pstmt = conn.prepareStatement (sql);    
			//supply values for prepared statement parameters
			pstmt.setString(1,action);
			pstmt.setString(2,user);			
			pstmt.setTimestamp(3,  now);
			pstmt.setString(4,commit_msg);
			pstmt.setString(5,f.getFeatureid());
			pstmt.setString(6,f.getFeaturetype());
			pstmt.setString(7,f.getMethod());
			pstmt.setString(8,f.getSegment());
			pstmt.setString(9,f.getStart());
			pstmt.setString(10,f.getEnd());
			pstmt.setString(11,f.getStrand());
			pstmt.setString(12,f.getPhase());		
			if (f.getScore()!=null){
				pstmt.setFloat(13,Float.valueOf(f.getScore()).floatValue());
			}
			else{
				pstmt.setNull(13, 6);
			}
			pstmt.setString(14, f.getNotes());	
	
			pstmt.setString(15, f.getFeatureid());	
			//execute insert statement
			pstmt.executeUpdate();
			return true;
		}
		catch (SQLException e){
			System.out.println("error "+e.getMessage().toString());
			e.printStackTrace();
			return false;}
	}

	private FeatureBean featureBeanFromRow(ResultSet rs)
	{
		try{
			FeatureBean f = new FeatureBean();
			//id | featureid | featuretype | method | segmentid       | start | end  
			//| strand | phase | score | attributes
			f.setId(rs.getString("id"));
			f.setFeatureid(rs.getString("featureid"));
			f.setFeaturetype(rs.getString("featuretype"));
			f.setMethod(rs.getString("method"));
			f.setSegment(rs.getString("segmentid"));
			f.setStart(rs.getString("start"));
			f.setEnd(rs.getString("end"));
			f.setStrand(rs.getString("strand"));
			f.setPhase(rs.getString("phase"));
			f.setScore(rs.getString("score"));
			f.setNotes(rs.getString("attributes"));
			return f;
		}
		catch (SQLException e){
			e.printStackTrace();
			return null;}
	}

	public FeatureBean[]  getAllFeatures(Connection conn, int id)
	{
		try{
			String tableName = getTableNameFromURI(id);

			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select id,featureid,featuretype,"+
					"method,segmentid,start,end,phase,strand,score, attributes" +
					" from " + tableName);	
			List features = new ArrayList();
			while (rs.next()) {
				FeatureBean f = featureBeanFromRow(rs);
				features.add(f);
			}
			rs.close();
			return (FeatureBean[]) features.toArray(new FeatureBean[features.size()]);
		}
		catch (SQLException e){
			e.printStackTrace();
			return null;}
	}

	public FeatureBean getFeatureById(Connection conn, 
			String feature_id,
			int systemid)
	{
		try{
			
			String sql = "select * from features_? where featureid= ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1,systemid);
			ps.setString(2,feature_id);
			ResultSet rs = ps.executeQuery();	
			FeatureBean f = new FeatureBean();
			if (rs.next()) {
				do {
					f = featureBeanFromRow(rs);
				} while (rs.next());
			}
			else {
				System.out.println("features table is empty");
			}
			return f;
		}
		catch (SQLException e){
			e.printStackTrace();
			return null;}
	}
	
	
	public FeatureBean[] getHistoryById(Connection conn, 
			String feature_id, 
			int systemid)
	{
		try{
			Statement stat = conn.createStatement();

			ResultSet rs = stat.executeQuery("select * from history_" +
					systemid + " where ref_id='" + feature_id+"'");	

			List features = new ArrayList();
			while (rs.next()) {
				FeatureBean f = featureBeanFromRow(rs);
				f.setAction(rs.getString("action"));
				f.setDate(rs.getString("date"));
				f.setUser(rs.getString("user"));
				f.setCommitMsg(rs.getString("commit_msg"));
				features.add(f);
			}

			rs.close();
			return (FeatureBean[]) features.toArray(new FeatureBean[features.size()]);
		}
		catch (SQLException e){
			e.printStackTrace();
			return null;}
	}
	
	public List getGroups(Connection conn, int systemid)
	{
		try{
			List groups = new ArrayList();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("show tables like 'groups_"+ systemid+"'");	
			if (rs.next()) {
				rs = stat.executeQuery("select groupid from groups_"+ systemid);	

				while (rs.next()) {
					groups.add(rs.getString("groupid"));
				}
			}
			else {
				System.out.println("groups table does not exist");
			}
			rs.close();
			return (groups);
		}
		catch (SQLException e){
			e.printStackTrace();
			return null;}
	}
	
	public String deleteFeature(Connection conn, FeatureBean[] features, 
			                  int systemid)
	{
        String reply="";
		try{

			//DataManager dbM = new DataManager();
			for (int i = 0; i < features.length; i++) {
				FeatureBean f = features[i];
				//get old feature data
				FeatureBean fb =getFeatureById(conn,f.getDelete(), systemid);
				
				if (fb.getFeatureid()!=""){
					//save old values into history table
					if (saveHistory(conn,fb, "D", f.getCommitMsg(), systemid,f.getUser())){
						String tableName = getTableNameFromURI(systemid);

						//prepare SQL statement
						String sql = "delete from " + tableName + 
						" where featureid='"+f.getDelete()+"'";

						PreparedStatement pstmt = null; 
						pstmt = conn.prepareStatement (sql);    
						//execute insert statement
						pstmt.executeUpdate();
						reply=RESULT_OK;
					}
					else reply = "Error! Could not save History table!";
				}
				else reply = "Error! This feature was not found in Features table! ";
			}
		}
		catch (SQLException e){
			e.printStackTrace();
			reply="Error! " + e.getMessage();}
		return reply;
	}

	public void saveCoordinate(Connection conn, String [][] coordinates)
	{

		if ( coordinates == null){
			System.err.println("did not get any coordinates. can not sync them with database");
			return ;
		}
		
		try{
			DataManager dbM = new DataManager();
			List db_tables = new ArrayList();
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select * from coordinate_system");	
			if (rs.next()) {
				do {
					db_tables.add(rs.getString(2).toString());
				} while (rs.next());
			}
			else {
				System.out.println("Coordinate system table is empty");
			}

			for (int i=0;i<coordinates.length;i++) {
				//String name=coordinates.get(i).toString();
                String name=coordinates[i][0];
                String URI =coordinates[i][1];                       
				// check existing tables in the database 
				boolean found;
				if (db_tables.size()>0)
					found = db_tables.contains(name);
				else found=false;

				if (found){
					System.out.println("Found! " + name);
				}
				else{
					//new entry found in COORDINATESYSTEM.XML
					System.out.println("NOT found "+coordinates[i][0]);
					// insert into coordinate_system table
					String sql = "insert into coordinate_system(" +
					"name,label,uri) values(?,?,?)";
					PreparedStatement pstmt = null; 
					pstmt = conn.prepareStatement (sql);  
					//prepare statement
					pstmt.setString(1,coordinates[i][0]);
					pstmt.setString(2,"");				
					pstmt.setString(3,coordinates[i][1]);
					pstmt.executeUpdate();

					//get last id
					rs = stat.executeQuery("select id from coordinate_system "+
							"where name like '"+coordinates[i][0] + "'");
					rs.first();
					//add new features_id table
					String tableName = getTableNameFromURI(getIdInt((rs.getString("id").toString())));
					sql = "create table "+ tableName +
					"(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,featureid VARCHAR(64) UNIQUE,"+
					" featuretype VARCHAR(32),method VARCHAR(32),segmentid VARCHAR(64),"+
					" start VARCHAR(32),end VARCHAR(32),strand CHAR(1),phase CHAR(1),score FLOAT,attributes TEXT)";
					pstmt = conn.prepareStatement (sql); 
					pstmt.executeUpdate();		    
					// add new history table
					sql = "create table history_"+ rs.getString("id") +
					"(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, action CHAR(1), user VARCHAR(200), date TIMESTAMP, commit_msg TEXT, featureid VARCHAR(64),"+
					" featuretype VARCHAR(32),method VARCHAR(32),segmentid VARCHAR(64),"+
					" start VARCHAR(32),end VARCHAR(32),strand CHAR(1),phase CHAR(1),score FLOAT,attributes TEXT,ref_ID VARCHAR(64))";
					pstmt = conn.prepareStatement (sql); 
					pstmt.executeUpdate();		    	
				}
			}
		}
		catch (SQLException e){
			e.printStackTrace();}
	}
	
	private int getIdInt(String ID){
		int id = -1;
		try {
			id = Integer.parseInt(ID);

		} catch (NumberFormatException ex){
			System.err.println("not valid coordinate system id");
		}
		return id;
	}
	
	public void saveGroups(Connection conn, List groups, int systemid)
	{
		try{
			Statement stat = conn.createStatement();
			PreparedStatement pstmt = null; 
			//check if table exists in db
			ResultSet rs = stat.executeQuery("show tables like 'groups_"+ systemid+"'");	
			if (!rs.next()) {
				String sql = "create table groups_"+ systemid +
				"(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,groupid varchar(64),attributes text)";
				pstmt = conn.prepareStatement (sql); 
				pstmt.executeUpdate();
			}
			// add new group
			String sql = "insert into groups_"+systemid +" (" +
			"groupid,attributes) values(?,?)";
			pstmt = conn.prepareStatement (sql);  
			//prepare prepared statement
			pstmt.setString(1,groups.get(0).toString());
			pstmt.setString(2,groups.get(1).toString());				 
			pstmt.executeUpdate();
		}
		catch (SQLException e){
			e.printStackTrace();}
	}
	
	
	public void deleteGroup(Connection conn, 
			                String group, 
			                int systemid)
	{
		try{
			Statement stat = conn.createStatement();
			PreparedStatement pstmt = null; 
			
			// delete a group
			String sql = "delete from groups_"+systemid +
			" where groupid='" + group + "'";
		
			pstmt = conn.prepareStatement (sql);  
			pstmt.executeUpdate();
		}
		catch (SQLException e){
			e.printStackTrace();}
	}
	
	public String getAttributeById(Connection conn, 
										String groupid,
										int systemid)
	{
		try{
			
			String sql = "select * from groups_? where groupid like ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1,systemid);
			ps.setString(2,groupid);
			ResultSet rs = ps.executeQuery();
			String attribute="";
			if (rs.next()) {
				attribute = rs.getString("attributes");
			}
			else {
				System.out.println("such group was not found");
			}
			return attribute;
		}
		catch (SQLException e){
			e.printStackTrace();
			return null;}
	}
	
	public FeatureBean[] getFeaturesByGroup(Connection conn, 
										 String groupid,
										 int systemid)
	{
		try{
			String tableName = getTableNameFromURI(systemid);
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery("select id,featureid,featuretype,"+
					"method,segmentid,start,end,phase,strand,score, attributes" +
					" from " + tableName + " where attributes like '%group=" + groupid+"'");	
			
			List features = new ArrayList();
	
			while (rs.next()) {
				FeatureBean f = featureBeanFromRow(rs);
				features.add(f);
			}
			rs.close();
			return (FeatureBean[]) features.toArray(new FeatureBean[features.size()]);
		}
		catch (SQLException e){
			e.printStackTrace();
			return null;}
	}
	
	
}

